package com.liangtian.mdm.util;

import com.liangtian.mdm.dm.config.model.AttrConfig;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

import static com.liangtian.mdm.util.ExportUtil.writeInJsp;

public class ExcelUtil {
    private static Logger log = LoggerFactory.getLogger(ExcelUtil.class);

    /**
     * excel导入
     *
     * @param modelId
     * @param input
     */
    public static List<Map<Integer, Map<String, String>>> excelImport(
            String modelId, InputStream input, List<AttrConfig> configLst) {
        DataFormatter formatter = new DataFormatter();
        List<Map<Integer, Map<String, String>>> attrValueLst = new ArrayList<Map<Integer, Map<String, String>>>();
        try {
            XSSFWorkbook workBook = new XSSFWorkbook(input);
            XSSFSheet sheet = workBook.getSheetAt(0);//获取EXCEL模板里面的第一个SHEET页
            Map<Integer, Map<String, String>> rowMap = new HashMap<Integer, Map<String, String>>();// 行map
            int lxkh = 5;
            int row_num = 0;
            for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {// 获取所有行
                XSSFRow row = sheet.getRow(i); // 获取一行
                if (null != row) {
                    Map<String, String> colMap = new HashMap<String, String>();
                    for (int j = 0; j < configLst.size(); j++) {// 这一行所有的单元格
                        String column = configLst.get(j).getAttrColumn().toLowerCase();
                        XSSFCell cell = row.getCell(j); // 这一行每个单元格
                        if (null != cell) {
                            switch (cell.getCellTypeEnum()) {
                                case STRING: // 字符串
                                    if (null != cell.getStringCellValue())
                                        colMap.put(column, cell.getStringCellValue().trim());
                                    break;
                                case NUMERIC: // 数字
                                    String num = formatter.formatCellValue(cell);
                                    colMap.put(column, num);
                                    break;
                                case BOOLEAN: // Boolean
                                    colMap.put(column, cell.getBooleanCellValue() + "");
                                    break;
                                case FORMULA: // 公式
                                    colMap.put(column, cell.getCellFormula() + "");
                                    /*    System.out.print(cell.getCellFormula() + "   ");   */
                                    break;
                                case BLANK: // 空值
                                    colMap.put(column, "");
                                    /* System.out.println(" ");   */
                                    break;
                                case ERROR: // 故障
                                    /*  System.out.println(" ");*/
                                    break;
                                default:
                                    /* System.out.print("未知类型   ");   */
                                    break;
                            }
                        } else {
                            colMap.put(column, null);
                        }
                    }
                    if (colMap.isEmpty()) {
                        if (i - 1 == row_num)
                            lxkh--;
                        if (0 == lxkh)
                            break;
                        row_num = i;
                        colMap = null;
                    }
                    rowMap.put(i + 1, colMap);
                } else {
                    if (0 == i) {
                        rowMap = null;
                        break;
                    } else
                        rowMap.put(i + 1, null);
                }
            }
            attrValueLst.add(rowMap);
            workBook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return attrValueLst;
    }


    /**
     * excel导入 刘晶添加新版
     *
     * @param modelId
     * @param input
     * @param jspAttrLst 在模板上可看到的操作列
     */
    public static Map<String, Object> excelSXSSWorkImport(
            String modelId,//模型ID
            InputStream input,//Excel流数据
            List<AttrConfig> jspAttrLst) {//Excel属性列
        DataFormatter formatter = new DataFormatter();
        Map<String, Object> resultMap = new HashMap<>();//返回的map集合 --这个集合是针对校验模板的
        Map<Integer, Map<String, String>> rowMap = new HashMap<Integer, Map<String, String>>();//（具体到行数） 回执有效行数据map集合
        //List<Map<String, String>> dataLst = new ArrayList<Map<String, String>>();
        StringBuilder errTxt = new StringBuilder();//定义返回属性校验结果集
        boolean checkHeader = true;//校验表头默认是成功的
//				int lxkh = 5;
        try {
            XSSFWorkbook workBook = new XSSFWorkbook(input);
            /*====== 1.首先判断模板里面是否有可操作的Sheet页	====================================*/
            int sheetNum = workBook.getNumberOfSheets();//获取当前工作薄中sheet页长度
            if (sheetNum > 0) {//表示没有可操作的sheet页
                XSSFSheet sheet = workBook.getSheetAt(0);//获取第一个sheet页的值
                /*====== 2.然后判断第一个可操作的Sheet页里面是否有可操作的数据	=============================*/
                int rows = sheet.getPhysicalNumberOfRows();//获取当前导入的excel表格里面有多少行数据
                if (rows > 0) {//如果行数大于0（如果是rows=1表示含有表头）
                    if (rows > 1) {//存在可操作的数据
                        /*====== 3.如果模板里面存在可操作的数据，那么现在就要验证Excel里面模板是否正确===================================*/
                        XSSFRow RowHeader = sheet.getRow(0); // 获取一行  RowHeader(表头)
                        if (!ObjectUtils.isEmpty(RowHeader)) {//判断表头是否为空
                            /*====== 4.获取表头的Cell单元格的数量====================================*/
                            int cellNubers = RowHeader.getPhysicalNumberOfCells(); // 这一行
                            if (cellNubers > 0) {
                                if (cellNubers == jspAttrLst.size()) {
                                    /*====== 5.校验模板表头是否正确   ====================================*/
                                    for (int i = 0; i < cellNubers; i++) {
                                        XSSFCell colVal = RowHeader.getCell(i); // 这一行每个单元格(针对表头)
                                        if (!ObjectUtils.isEmpty(colVal)) {
                                            if (!colVal.toString().equals(jspAttrLst.get(i).getAttrName())) {
                                                errTxt.append("此导入模板表头单元格名称和模型属性设置名称不同，导入模板表头有问题，请检查。。。$RN$");
                                                checkHeader = false;//校验不成功
                                            }
                                        } else {
                                            errTxt.append("此导入模板表头单元格存在空值，导入模板表头有问题，请检查。。。$RN$");
                                            checkHeader = false;//校验不成功
                                        }
                                    }
                                } else {
                                    errTxt.append("此导入模板表头单元格数为空，导入模板表头有问题，请检查。。。$RN$");
                                    checkHeader = false;//校验不成功
                                }
                            } else {
                                errTxt.append("此导入模板表头有效的操作单元格为空，导入模板有问题，请检查。。。$RN$");
                                checkHeader = false;//校验不成功
                            }
                        } else {
                            errTxt.append("此导入模板表头有效的操作单元格为空，导入模板有问题，请检查。。。$RN$");
                            checkHeader = false;//校验不成功
                        }
                        //判断模板校验是否成功，如果成功的话，那么开始封装数据
                        if (checkHeader) {
                            for (int i = 1; i <= rows - 1; i++) {// 获取所有行(从第二行开始)
                                XSSFRow row = sheet.getRow(i); // 获取一行
                                Map<String, String> colMap = new HashMap<String, String>();
                                for (int j = 0; j < jspAttrLst.size(); j++) {// 这一行所有的单元格
                                    String column = jspAttrLst.get(j).getAttrColumn().toLowerCase();
                                    XSSFCell cell = row.getCell(j); // 这一行每个单元格
                                    if (!ObjectUtils.isEmpty(cell)) {//如果单元格的值不为空
                                        switch (cell.getCellTypeEnum()) {
                                            case STRING: // 字符串
                                                if (null != cell.getStringCellValue())
                                                    colMap.put(column, cell.getStringCellValue().trim());
                                                break;
                                            case NUMERIC: // 数字
                                                String num = formatter.formatCellValue(cell);
                                                colMap.put(column, num);
                                                break;
                                            case BOOLEAN: // Boolean
                                                colMap.put(column, cell.getBooleanCellValue() + "");
                                                break;
                                            case FORMULA: // 公式
                                                colMap.put(column, cell.getCellFormula() + "");
                                                /*    System.out.print(cell.getCellFormula() + "   ");   */
                                                break;
                                            case BLANK: // 空值
                                                colMap.put(column, "");
                                                /* System.out.println(" ");   */
                                                break;
                                            case ERROR: // 故障
                                                /*  System.out.println(" ");*/
                                                break;
                                            default:
                                                /* System.out.print("未知类型   ");   */
                                                break;
                                        }
                                    }
                                }
                                rowMap.put(i, colMap);
                            }
                        }
                    } else {//不存在可操作的数据
                        errTxt.append("此导入模板没有有效的操作数据，属于空模板，请收集相关数据。。。$RN$");
                    }
                } else {
                    errTxt.append("此导入模板没有表头，导入模板有问题，请检查。。。$RN$");
                }
            } else {
                errTxt.append("此导入模板没有有效的Sheet操作页，导入模板有问题，请检查。。。$RN$");
            }

            resultMap.put("errTxt", errTxt.toString());//模板的错误原因
            resultMap.put("dataLst", rowMap);//数据
            workBook.close();//关闭工作薄
            input.close();//关闭工作流
        } catch (Exception e) {
            e.printStackTrace();
            //errTxt.append("后台报错信息_如下所示：$RN$"+e.getMessage());
        }
        return resultMap;
    }


    public static List<Map<Integer, Map<Integer, String>>> excelImport2(InputStream input, String modelId) {
        List<Map<Integer, Map<Integer, String>>> attrValueLst = new ArrayList<Map<Integer, Map<Integer, String>>>();
        try {
            DataFormatter formatter = new DataFormatter();
            XSSFWorkbook workBook = new XSSFWorkbook(input);
            XSSFSheet sheet = workBook.getSheetAt(0);
            Map<Integer, Map<Integer, String>> rowMap = new HashMap<Integer, Map<Integer, String>>();// 行map
            int lxkh = 5;
            int row_num = 0;
            for (int i = 0; i < sheet.getPhysicalNumberOfRows(); i++) {// 获取所有行
                XSSFRow row = sheet.getRow(i); // 获取一行
                if (null != row) {
                    Map<Integer, String> colMap = new HashMap<Integer, String>();
                    Iterator<Cell> cells = row.cellIterator();
                    int m = 1;
                    while (cells.hasNext()) {
                        Cell cell = (Cell) cells.next();
                        if (null != cell) {
                            switch (cell.getCellTypeEnum()) {
                                case STRING: // 字符串
                                    if (null != cell.getStringCellValue())
                                        colMap.put(m, cell.getStringCellValue().trim());
                                    //System.out.print(cell.getStringCellValue()+ "   ");
                                    break;
                                case NUMERIC: // 数字
                                    //colMap.put(m, String.valueOf(cell.getNumericCellValue()));
                                    String num = formatter.formatCellValue(cell);
                                    //System.out.print(num+ "   ");
                                    colMap.put(m, num);
                                    break;
                                case BOOLEAN: // Boolean
                                    colMap.put(m, cell.getBooleanCellValue() + "");
                                    break;
                                case FORMULA: // 公式
                                    colMap.put(m, cell.getCellFormula() + "");
                                    /*    System.out.print(cell.getCellFormula() + "   ");   */
                                    break;
                                case BLANK: // 空值
                                    colMap.put(m, null);
                                    /* System.out.println(" ");   */
                                    break;
                                case ERROR: // 故障
                                    /*  System.out.println(" ");*/
                                    break;
                                default:
                                    /* System.out.print("未知类型   ");   */
                                    break;
                            }
                        }
                        m++;
                    }
                    if (colMap.isEmpty()) {
                        if (i - 1 == row_num)
                            lxkh--;
                        if (0 == lxkh)
                            break;
                        row_num = i;
                        colMap = null;
                    }
                    rowMap.put(i + 1, colMap);
                } else {
                    if (0 == i) {
                        rowMap = null;
                        break;
                    } else
                        rowMap.put(i + 1, null);
                }
            }
            attrValueLst.add(rowMap);
            workBook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return attrValueLst;

    }

    @SuppressWarnings("deprecation")
    public static Cell addCell(XSSFSheet sheet, Row row, String str, int columnIndex, CellStyle style, List<String> attrTypeLst) {
        Cell cell = row.createCell(columnIndex);
        cell.setCellValue(str);

        if (style != null)
            cell.setCellStyle(style);
        if (null != attrTypeLst) {
            String[] arr = (String[]) attrTypeLst.toArray(new String[attrTypeLst.size()]);
            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
            // 生成下拉框内容
            XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(arr);
            CellRangeAddressList regions = null;

            XSSFDataValidation validation = null;

            for (int i = 0; i < attrTypeLst.size(); i++) {
                // 绑定下拉框和作用区域
                regions = new CellRangeAddressList(1, 1000, 1, 1);
                validation = (XSSFDataValidation) dvHelper.createValidation(
                        dvConstraint, regions);
                // 对sheet页生效
                sheet.addValidationData(validation);
            }
        }
        return cell;
    }

    @SuppressWarnings("deprecation")
    public static Cell addHFCell(HSSFSheet sheet, Row row, String str, int columnIndex, HSSFCellStyle style, List<String> attrTypeLst) {
        //Cell cell = row.createCell(columnIndex);
        //	cell.setCellValue(str);
        HSSFCell cell = (HSSFCell) row.createCell(columnIndex);//创建导出来的excel表格的单元格形式
        cell.setCellValue(str);
        cell.setCellType(CellType.STRING);//设置为文本格式
        if (style != null)
            cell.setCellStyle(style);
        if (null != attrTypeLst) {
            String[] arr = (String[]) attrTypeLst.toArray(new String[attrTypeLst.size()]);
            //XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
            HSSFDataValidationHelper dvHelper = new HSSFDataValidationHelper(sheet);
            // 生成下拉框内容
            //XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
            // .createExplicitListConstraint(arr);
            DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(arr);
            //CellRangeAddressList regions = null;
            CellRangeAddressList regions = null;
            HSSFDataValidation validation = null;
            //XSSFDataValidation validation = null;

            for (int i = 0; i < attrTypeLst.size(); i++) {
                // 绑定下拉框和作用区域
                regions = new CellRangeAddressList(1, 1000, 1, 1);
                //validation = (XSSFDataValidation) dvHelper.createValidation(
                // dvConstraint, regions);
                validation = (HSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
                // 对sheet页生效
                sheet.addValidationData(validation);
            }
        }
        return cell;
    }


    /**
     * <p>TODO 导出模板最新的方法</p>
     * <p>@param sheet
     * <p>@param row
     * <p>@param str
     * <p>@param columnIndex
     * <p>@param style
     * <p>@param attrTypeLst
     * <p>@return </p>
     *
     * @author : xiaoliu
     * @date :2017年11月9日上午10:10:44
     */
    @SuppressWarnings("deprecation")
//	sheet,row,jspAttrLst.get(i).getAttrName(),i,redStyle,null
    public static Cell addSXSSWorkCell(Sheet sheet, Row row, String str, int columnIndex, CellStyle style, List<String> attrTypeLst) {
        Cell cell = row.createCell(columnIndex);//创建导出来的excel表格的单元格形式
        cell.setCellType(CellType.STRING);
        cell.setCellValue(str);
        if (style != null)
            cell.setCellStyle(style);
        if (null != attrTypeLst) {
            String[] arr = (String[]) attrTypeLst.toArray(new String[attrTypeLst.size()]);
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            DataValidationConstraint dvConstraint = dvHelper.createExplicitListConstraint(arr);
            DataValidation validation = null;
            CellRangeAddressList regions = null;
            for (int i = 0; i < attrTypeLst.size(); i++) {
                // 绑定下拉框和作用区域
                regions = new CellRangeAddressList(1, 1000, 1, 1);
                validation = dvHelper.createValidation(dvConstraint, regions);
                //处理Excel兼容性问题
                if (validation instanceof XSSFDataValidation) {
                    validation.setSuppressDropDownArrow(true);
                    validation.setShowErrorBox(true);
                } else {
                    validation.setSuppressDropDownArrow(false);
                }
                sheet.addValidationData(validation);
            }
        }
        return cell;
    }

    /**
     * @param sheet       excel
     * @param row         行
     * @param factory
     * @param columnIndex 列标
     * @param style       样式
     * @param attrTypeLst 下拉选框
     * @param val         值及批注
     * @return
     */
    public static Cell addCell(XSSFSheet sheet, Row row, CreationHelper factory,
                               int columnIndex, CellStyle style, List<String> attrTypeLst, String... val) {
        Cell cell = row.createCell(columnIndex);
        cell.setCellValue(val[0]);

        Drawing drawing = sheet.createDrawingPatriarch();
        // When the comment box is visible, have it show in a 1x3 space
        ClientAnchor anchor = factory.createClientAnchor();
        anchor.setCol1(cell.getColumnIndex());
        anchor.setCol2(cell.getColumnIndex() + 2);
        anchor.setRow1(row.getRowNum());
        anchor.setRow2(row.getRowNum() + 4);
        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);

        // Create the comment and set the text+author
        Comment comment = drawing.createCellComment(anchor);
        RichTextString str = factory.createRichTextString(val[1]);
        comment.setString(str);
        comment.setAuthor("admin");
        // Assign the comment to the cell
        cell.setCellComment(comment);

        if (style != null)
            cell.setCellStyle(style);
        if (null != attrTypeLst) {
            String[] arr = (String[]) attrTypeLst
                    .toArray(new String[attrTypeLst.size()]);
            XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
            // 生成下拉框内容
            XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper
                    .createExplicitListConstraint(arr);
            CellRangeAddressList regions = null;

            XSSFDataValidation validation = null;

            for (int i = 0; i < attrTypeLst.size(); i++) {
                // 绑定下拉框和作用区域
                regions = new CellRangeAddressList(1, 1000, 1, 1);
                validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, regions);
                // 对sheet页生效
                sheet.addValidationData(validation);
            }
        }
        //sheet.autoSizeColumn(columnIndex);
        return cell;
    }

    /**
     * 设置响应头
     */
    public static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            if (!StringUtil.isBlank(fileName)) {
                fileName = fileName + ".xlsx";
            } else {
                fileName = "属性模板.xlsx";
            }
            fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
            response.setHeader("Content-disposition", "attachment; filename=" + fileName);// 设定输出文件头
            response.setContentType("application/msdownload");// 定义输出类型
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //导出Excel
    public static void setHFResponseHeader(HttpServletResponse response, String fileName) {
        try {
            if (!StringUtil.isBlank(fileName)) {
                fileName = fileName + ".xlsx";
            } else {
                fileName = "属性模板.xlsx";
            }
            fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
            response.setHeader("Content-disposition", "attachment; filename=" + fileName);// 设定输出文件头
            response.setContentType("application/msdownload");// 定义输出类型
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //导出Excel
    public static void setSXSSWorkResponseHeader(HttpServletResponse response, String fileName) {
        try {
            if (!StringUtil.isBlank(fileName)) {
                fileName = fileName + ".xlsx";
            } else {
                fileName = "属性模板.xlsx";
            }
            fileName = new String(fileName.getBytes("GBK"), "iso8859-1");
            response.setHeader("Content-disposition", "attachment; filename=" + fileName);// 设定输出文件头
            response.setContentType("application/msdownload");// 定义输出类型
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 写入错误文件
     *
     * @param errText
     * @param fileName
     */
    public void errTxt(String errText, String fileName) throws IOException {

        String[] strs = errText.split("@@");
        StringBuffer sbStr = new StringBuffer();
        for (String str : strs) {
            sbStr.append(str);
        }
        String path = IoUtil.getModelPath() + "/" + fileName;
        log.info("---wangyue---" + path);
        writeInJsp(sbStr.toString(), path);
    }

    /**
     * 校验excel空行
     *
     * @param row 行对象
     * @return
     */
    public static boolean isRowEmpty(Row row) {
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && cell.getCellType() != CellType.BLANK.getCode())
                return false;
        }
        return true;
    }

    public static CellStyle createCellTextStyle(XSSFWorkbook wb) {
        CellStyle style1 = wb.createCellStyle();
        DataFormat format = wb.createDataFormat();
        style1.setDataFormat(format.getFormat("@"));
        return style1;
    }
}
